from pymysql import connect, Error
from pymysql.cursors import DictCursor

# 用with语法改造
# with语法的好处就是打开了 可以自动关闭
try:
    # 建立连接
    conn = connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='my_news',
        # 将结果转换为字典
        cursorclass=DictCursor
    )
    with conn:
        with conn.cursor() as cursor:
            # sql = 'INSERT INTO school_student_info(stu_no,stu_name,class_name) VALUES(1008,"王李明","三年级二班");'
            # 执行sql
            # cursor.execute(sql)

            # stu_no = 1009
            # stu_name = "王1"
            # class_name = "三年级二班"
            # sql = 'INSERT INTO school_student_info(stu_no,stu_name,class_name) VALUES(%s,%s,%s);'
            # # 执行sql
            # cursor.execute(sql, (stu_no, stu_name, class_name))

            #   批量新增mysql数据executemany
            # sql = 'INSERT INTO school_student_info(stu_no,stu_name,class_name) VALUES(%s,%s,%s);'
            # data_list = ((1010, '学生1', '三年级二班'), (1011, '学生2', '三年级二班'), (1012, '学生3', '三年级二班'))
            # cursor.executemany(sql, data_list)
            # 提交事务
            # conn.commit()

            # 事务的提交与回滚
            # sql1 = 'INSERT INTO school_student_info(stu_no,stu_name,class_name) VALUES(1013,"王李明1","三年级二班");'
            # sql2 = 'INSERT INTO school_course_info(course_name,teacher,`desc`) VALUES("地理","王老师","sfjoidsfjo");'
            # # 开始事务
            # conn.begin()
            # cursor.execute(sql1)
            # cursor.execute(sql2)
            # conn.commit()

            # 遇错回滚
            sql0 = 'INSERT INTO school_student_info(stu_no,stu_name,class_name) VALUES(101312,"王李明1211","三年级二班");'
            cursor.execute(sql0)
            # 开始事务
            conn.begin()
            try:
                sql1 = 'INSERT INTO school_student_info(stu_no,stu_name,class_name) VALUES(10132,"王李明12","三年级二班");'
                cursor.execute(sql1)
            except Exception as e:
                print(e)

            try:
                # 这儿会报错  因为desc是必填项  事务会回滚到开始事务的地方
                sql2 = 'INSERT INTO school_course_info(course_name,teacher,`desc`) VALUES("地理2","王老师1");'
                cursor.execute(sql2)
            except Exception as e:
                print(e)
                # 回滚事务
                conn.rollback()
                print('回滚成功')
            # 提交事务
            conn.commit()
            print('执行完成')
except Error as e:
    print('连接失败：{}'.format(e))
